PostgreSQL 查询进阶

PostgreSQL 系统讲解第三篇：PG 查询能力里"MySQL 学不会或不顺手"的部分——CTE（含递归）、窗口函数、UPSERT。学完这三样，复杂查询的写法会上一个台阶。

CTE：WITH 子句

WITH hot_users AS (
    SELECT id, name FROM users WHERE created_at > now() - INTERVAL '30 days'
),
order_stats AS (
    SELECT user_id, count(*) cnt, sum(amount) total
    FROM orders GROUP BY user_id
)
SELECT h.name, o.cnt, o.amount
FROM hot_users h JOIN order_stats o ON o.user_id = h.id;

CTE = 给中间结果起名字，复杂查询从"一层套一层"变成"一步步搭积木"——可读性比子查询嵌套强一个数量级。

递归 CTE：查树/层级结构（组织架构、分类树）的杀手锏：

WITH RECURSIVE sub_tree AS (
    SELECT id, name, parent_id FROM dept WHERE id = 1      -- 起点
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM dept d JOIN sub_tree s ON d.parent_id = s.id      -- 递归：上一轮结果找孩子
)
SELECT * FROM sub_tree;

MySQL 8.0 也有递归 CTE 了，但 PG 里这是"日常工具"级别的存在。

窗口函数：不折叠行地做聚合

GROUP BY 会合并行，窗口函数保留每一行，旁边挂上"组内计算结果"：

-- 每个订单旁边挂上：它在自己用户订单里的金额排名、全表平均
SELECT id, user_id, amount,
       rank()    OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_in_user,
       avg(amount) OVER (PARTITION BY user_id)                    AS avg_of_user,
       amount - avg(amount) OVER (PARTITION BY user_id)           AS diff_from_avg
FROM orders;

-- 全局 top5（不用 GROUP BY 丢行）
SELECT name, score, rank() OVER (ORDER BY score DESC) AS rk FROM players LIMIT 5;

常用件：rank / dense_rank / row_number（并列处理不同）、lag / lead（上一行/下一行，算环比利器）、sum() OVER (ORDER BY ts)（累计值）。

我的记忆钩子："每行都要、又要组内统计"就是窗口函数的信号——比如"每个部门工资最高的人"（用 GROUP BY 会丢掉其他列）。

UPSERT：ON CONFLICT

MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 在 PG 里对应：

INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, now())
ON CONFLICT (user_id) DO UPDATE
    SET login_count = user_stats.login_count + 1, last_login = EXCLUDED.last_login;

ON CONFLICT (user_id) DO NOTHING;    -- 只是想避免重复插入

EXCLUDED 引用"试图插入的那行"，冲突处理比 MySQL 语义更明确（指定冲突列）。

其他顺手的查询能力

RETURNING：INSERT/UPDATE/DELETE 直接返回受影响行——省一次 SELECT（01 篇演示过取自增 id）
ILIKE / 正则：name ILIKE '%zh%'、name ~ '^(张|李)'
DISTINCT ON：PG 独有——"每组取第一条"一个子句搞定：

SELECT DISTINCT ON (user_id) user_id, ts, action
FROM logs ORDER BY user_id, ts DESC;     -- 每个用户最近一条日志

LATERAL：子查询引用前面表的列（"每用户取最新 3 单"这类 Top-N per group 的高效写法）

全文检索入门

SELECT to_tsvector('simple', 'Redis 是内存数据库') @@ to_tsquery('simple', 'Redis');
-- 中文要配 zhparser/pg_jieba 扩展做分词（动手时核对）

CREATE INDEX ON docs USING GIN (to_tsvector('simple', content));

定位：内置全文检索适合"够用就好"的场景；专业搜索仍上 ES——但 GIN + tsvector 能顶掉一半的"给内容加个搜索"需求，不用为小需求再养一个中间件。

⬅️ 02-PostgreSQL 数据类型与表设计 🏠 00-数据库 ➡️ 04-PostgreSQL 索引与 MVCC

03-PostgreSQL 查询进阶